package io.renren.common.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;

import io.renren.common.exception.RRException;
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.springframework.web.multipart.MultipartFile;

/**
 * excel 读取公共类
 */
public class ExcelReaderUtil {

    /**
     *
     * xlsx文件读取函数
     *
     * 根据文件url解析excel
     * @param file 文件
     * @param args 读取得列序号 （0,1,2,4,5,6）
     * @return
     * @throws IOException
     */
    public static ArrayList<ArrayList<String>> excelReader(MultipartFile file, int ... args) throws IOException {
        String fileName = file.getOriginalFilename();
        System.out.println("文件名称："+fileName);
        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            throw new RRException("上传文件格式不正确");
        }
        InputStream is = file.getInputStream();
        return excelReader(is,args);
    }

    /**
     *
     * xlsx文件读取函数
     *
     * 根据文件url解析excel
     * @param excel_url 文件路径
     * @param args 读取得列序号
     * @return
     * @throws IOException
     */
    public static ArrayList<ArrayList<String>> excelReader(String excel_url,int ... args) throws IOException {
        //寻找目录读取文件
        File excelFile = new File(excel_url);
        InputStream is = new FileInputStream(excelFile);
        return excelReader(is,args);
    }

    /**
     *
     *  xlsx文件读取函数
     *
     * @param is excel输入流
     * @param args arg为需要查询的列号
     * @return
     * @throws IOException
     */
	public  static ArrayList<ArrayList<String>> excelReader(InputStream is,int ... args) throws IOException {
 
        //读取xlsx文件
        Workbook workbook = null;
        try {
            workbook = WorkbookFactory.create(is);
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        }

        if(workbook==null){
        	System.out.println("未读取到内容,请检查路径！");
        	return null;
        }

        ArrayList<ArrayList<String>> ans=new ArrayList<ArrayList<String>>();
        //遍历xlsx中的sheet
        for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
            Sheet sheet = workbook.getSheetAt(numSheet);
            if (sheet == null) {
                continue;
            }
            // 对于每个sheet，读取其中的每一行
            for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
                Row row = sheet.getRow(rowNum);
                if (row == null) continue;
                ArrayList<String> curarr=new ArrayList<String>();
                for(int columnNum = 0 ; columnNum<args.length ; columnNum++){
                	Cell cell = row.getCell(args[columnNum]);
                	
                	curarr.add(Trim_str(getValue(cell)));
                }
                ans.add(curarr);
            }
        }
        return ans;
    }
    
    //判断的excel文件的数据类
    @SuppressWarnings("deprecation")
	private static String getValue(Cell row) {
    	if(row==null){
    		return "";
    	}
        if (row.getCellTypeEnum() == CellType.BOOLEAN) { //布尔型
            return String.valueOf(row.getBooleanCellValue());
        } else if (row.getCellTypeEnum() == CellType.NUMERIC) { // 数值型
            Object inputValue = null;
            if (HSSFDateUtil.isCellDateFormatted(row)) {
                Date date = row.getDateCellValue();
                inputValue = DateFormatUtils.format(date, "yyyy-MM-dd");
                return String.valueOf(inputValue);
            }else{
                double cur=row.getNumericCellValue();
                long longVal = Math.round(cur);

                if(Double.parseDouble(longVal + ".0") == cur)
                    inputValue = longVal;
                else
                    inputValue = cur;
                return String.valueOf(inputValue);
            }
        } else if(row.getCellTypeEnum() == CellType.BLANK || row.getCellTypeEnum() == CellType.ERROR){ //空值 错误
        	return "";
        }else if (row.getCellTypeEnum() == CellType.FORMULA) { // 公式类型
            try {
                return String.valueOf(row.getStringCellValue());
            } catch (IllegalStateException e) {
                return String.valueOf(row.getNumericCellValue());
            }
        }else {
            return String.valueOf(row.getStringCellValue());
        }
    }
    
  //字符串修剪  去除所有空白符号 ， 问号 ， 中文空格
    static private String Trim_str(String str){
        if(str==null)
            return null;
        return str.replaceAll("[\\s\\?]", "").replace("　", "");
    }
}
